-- @owner:dingwenxuan
-- @date:2024-03-22
-- @testpoint:35.游标参数默认值在触发器场景下的应用,部分用例合理报错

--step1:创建测试表topengauss_cursor_param_default_case0035;expect:成功(1 row)

CREATE TABLE topengauss_cursor_param_default_case0035 (  
    id SERIAL PRIMARY KEY,  
    name VARCHAR(100),  
    age INT  
);

--step2:插入测试数据;expect:成功(3 row)
insert into topengauss_cursor_param_default_case0035 values (1,'小壮',17);
insert into topengauss_cursor_param_default_case0035 values (2,'小美',17);
insert into topengauss_cursor_param_default_case0035 values (3,'小丽',20);

--step3:创建函数fopengauss_cursor_param_default_case0035_1;expect:成功(1 row)
CREATE OR REPLACE FUNCTION fopengauss_cursor_param_default_case0035_1(age_param INT DEFAULT NULL) RETURNS VOID AS $$
  
DECLARE  
    v_result topengauss_cursor_param_default_case0035%ROWTYPE;
    CURSOR curopengauss_cursor_param_default_case0035 FOR  
        SELECT * FROM topengauss_cursor_param_default_case0035 WHERE (age_param IS NULL OR age = age_param);  
BEGIN  
    OPEN curopengauss_cursor_param_default_case0035;  
    LOOP  
        FETCH curopengauss_cursor_param_default_case0035 INTO v_result;  
        EXIT WHEN NOT FOUND;  
        RAISE NOTICE 'Result %', v_result;
    END LOOP;  
    CLOSE curopengauss_cursor_param_default_case0035;  
	
END;
$$ LANGUAGE plpgsql;
/

--step4:创建函数fopengauss_cursor_param_default_case0035_2;expect:成功(1 row)
CREATE OR REPLACE FUNCTION fopengauss_cursor_param_default_case0035_2() RETURNS TRIGGER AS $$
  
BEGIN    
    PERFORM fopengauss_cursor_param_default_case0035_1(NEW.age);  
    RETURN NEW;  
END;
$$ LANGUAGE plpgsql;  
/


 
--step5:创建triopengauss_cursor_param_default_case0035触发器;expect:成功(1 row)  
CREATE TRIGGER triopengauss_cursor_param_default_case0035
AFTER INSERT ON topengauss_cursor_param_default_case0035
FOR EACH ROW 
EXECUTE PROCEDURE fopengauss_cursor_param_default_case0035_2();
/

--step6:验证结果;expect:成功(3 row)
insert into topengauss_cursor_param_default_case0035 values (4,'小刚',17);

--step7:清理环境;expect:成功(3 row)
drop table topengauss_cursor_param_default_case0035 CASCADE;
drop FUNCTION fopengauss_cursor_param_default_case0035_1;
drop FUNCTION fopengauss_cursor_param_default_case0035_2;
